package com.scalar.chiptrack.externalusers.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.MessageDigestUtils;
import com.scalar.chiptrack.externalusers.forms.ExternalUserSetupForm;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.users.FunctionAccess;
import com.scalar.ScalarException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.HashMap;

/**
 * Created by IntelliJ IDEA.
 * User: Krishna Prasad
 * Date: Jun 29, 2004
 * Time: 4:09:42 PM
 * To change this template use Options | File Templates.
 */
public class ExternalUserSetupDAO
{

    /**
     * Inserts or updates the external user details
     *
     * @param dbTransaction
     * @param externalUser
     * @param isInsert
     * @throws ScalarException
     */
    public static void insertExternalUser( DBTransaction dbTransaction, User externalUser, boolean isInsert ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int[] selectedFunctionalAccess = {1, 1, 1};
        String accessStr = "";

        try
        {

            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if ( isInsert == false )
            {
                // Added by Veeru on 17th Nov 2004
                if ( externalUser.getPassword() == null || "".equals( externalUser.getPassword() ) )
                {
                    pstmt = conn.prepareStatement( SqlQueryMappings.get( "externalUsers.updateExternalUser" ) );
                    pstmt.setString( 1, externalUser.getFirstName() );
                    pstmt.setString( 2, externalUser.getLastName() );
                    pstmt.setString( 3, externalUser.getPhoneNumber() );
                    pstmt.setString( 4, externalUser.getFaxNumber() );
                    pstmt.setString( 5, externalUser.getEmail() );
                    if ( externalUser.getExpiresOn() != null && externalUser.getExpiresOn().trim().length() > 0 )
                    {
                        pstmt.setDate( 6, StringUtils.getSQLDate( externalUser.getExpiresOn(), null ) );
                    }
                    else
                    {
                        pstmt.setString( 6, null );
                    }
                    pstmt.setInt( 7, externalUser.getVendorId() );
                    pstmt.setString( 8, externalUser.getUserId() );
                }
                else
                {
                    pstmt = conn.prepareStatement( SqlQueryMappings.get( "externalUsers.updateExternalUserAndPassword" ) );
                    pstmt.setString( 1, externalUser.getPassword() );
                    pstmt.setString( 2, externalUser.getFirstName() );
                    pstmt.setString( 3, externalUser.getLastName() );
                    pstmt.setString( 4, externalUser.getPhoneNumber() );
                    pstmt.setString( 5, externalUser.getFaxNumber() );
                    pstmt.setString( 6, externalUser.getEmail() );
                    if ( externalUser.getExpiresOn() != null && externalUser.getExpiresOn().trim().length() > 0 )
                    {
                        pstmt.setDate( 7, StringUtils.getSQLDate( externalUser.getExpiresOn(), null ) );
                    }
                    else
                    {
                        pstmt.setString( 7, null );
                    }
                    pstmt.setInt( 8, externalUser.getVendorId() );
                    pstmt.setString( 9, externalUser.getUserId() );
                }
            }
            else
            {
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "externalUsers.insertExternalUser" ) );
                pstmt.setString( 1, externalUser.getUserId() );
                pstmt.setString( 2, externalUser.getPassword() );
                pstmt.setString( 3, externalUser.getFirstName() );
                pstmt.setString( 4, externalUser.getLastName() );
                pstmt.setString( 5, externalUser.getPhoneNumber() );
                pstmt.setString( 6, externalUser.getFaxNumber() );
                pstmt.setString( 7, externalUser.getEmail() );
                if ( externalUser.getExpiresOn() != null && externalUser.getExpiresOn().trim().length() > 0 )
                {
                    pstmt.setDate( 8, StringUtils.getSQLDate( externalUser.getExpiresOn(), null ) );
                }
                else
                {
                    pstmt.setString( 8, null );
                }
                pstmt.setInt( 9, externalUser.getVendorId() );
            }
            pstmt.executeUpdate();
            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not insert External User Info into database", sqle );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static boolean isExternalUserIdExist( DBTransaction dbTransaction, String externalUserId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "externalUsers.getExternalUser" ) );
            pstmt.setString( 1, externalUserId );
            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                success = true;
            }
            else
            {
                success = false;
            }

        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not Get External User Id From database", sqle );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return success;
    }

    public static ArrayList loadAllExternalUsers( DBTransaction dbTransaction, ExternalUserSetupForm extUsrForm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList extUserList = new ArrayList();
        ResultSet rset = null;

        String searchFName = extUsrForm.getSearchFirstName();
        String searchLName = extUsrForm.getSearchLastName();
        String sqlQuery = "";


        try
        {

            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            sqlQuery = SqlQueryMappings.get( "externalUsers.getAllExternalUsers" );

            if ( ( searchFName != null && searchFName.trim().length() > 0 ) || ( searchLName != null && searchLName.trim().length() > 0 ) )
            {
                sqlQuery = sqlQuery + " And First_Name like '" + searchFName + "%' And Last_Name like '" + searchLName + "%' ";
            }

            pstmt = conn.prepareStatement( sqlQuery );
            rset = pstmt.executeQuery();
            ExternalUserSetupForm eusf = null;

            while ( rset.next() )
            {
                eusf = new ExternalUserSetupForm();

                eusf.setExternalUserId( rset.getString( "User_Id" ) );
                eusf.setPassword( rset.getString( "Password" ) );
                eusf.setReEnterPassword( rset.getString( "Password" ) );
                eusf.setFirstName( rset.getString( "First_Name" ) );
                eusf.setLastName( rset.getString( "Last_Name" ) );
                eusf.setPhoneNumber( rset.getString( "Phone_Number" ) );
                eusf.setFaxNumber( rset.getString( "Fax_Number" ) );
                eusf.setEmail( rset.getString( "Email" ) );
                eusf.setExpiresOn( StringUtils.formatDateToString( rset.getDate( "Expires_On" ) ) );
                eusf.setDisabled((rset.getInt("Disabled") == 0) ? true : false);
                eusf.setUserType( rset.getInt ( "USER_TYPE" ) );
                extUserList.add( eusf );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load All Users from database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return extUserList;
    }

    /**
     * This method loads the external user details pertaining to the given userID.
     *
     * @param dbTransaction
     * @param externalUserId
     * @return user
     * @throws ScalarException
     */
    public static User getExternalUser( DBTransaction dbTransaction, String externalUserId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        User user = null;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "externalUsers.getExternalUser" ) );
            pstmt.setString( 1, externalUserId );
            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                user = new User();
                user.setUserId( rs.getString( "ExternalUser_Id" ) );
                user.setFirstName( rs.getString( "First_Name" ) );
                user.setLastName( rs.getString( "Last_Name" ) );
                user.setPhoneNumber( rs.getString( "Phone_Number" ) );
                user.setFaxNumber( rs.getString( "Fax_Number" ) );
                user.setEmail( rs.getString( "Email" ) );
                user.setExpiresOn( StringUtils.formatDateToString( rs.getDate( "Expires_On" ) ) );
                user.setVendorId( rs.getInt( "Vendor_ID" ) );
            }

        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Error while loading the external user details", sqle );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return user;
    }

    public static User loadExternalUser( String userId, String password ) throws ScalarException
    {

        if ( ( userId == null ) || ( userId.trim().length() == 0 ) )
        {
            return null;
        }

        User user = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        try
        {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "externalUsers.getExternalUser" ) );
            pstmt.setString( 1, userId );
            rset = pstmt.executeQuery();

            if ( rset.next() == false )
            {
                throw new ScalarException( "Invalid user id" );
            }
            else if ( ( password == null ) || ( MessageDigestUtils.verify( password.trim(), StringUtils.toByteArray( rset.getString( "Password" ).trim(), "," ) ) == true ) )
            {
                user = new User();
                user.setUserId( rset.getString( "ExternalUser_Id" ) );
                user.setFirstName( rset.getString( "First_Name" ) );
                user.setLastName( rset.getString( "Last_Name" ) );
                user.setEmail( rset.getString( "Email" ) );
                user.setExpiresOn( rset.getString( "Expires_On" ) );
                user.setVendorId( rset.getInt( "Vendor_ID" ) );
                rset.close();
                pstmt.close();
                pstmt = null;

                // Load functionality Access Map
                HashMap functionAccessMap = new HashMap();
                pstmt = conn.prepareStatement(SqlQueryMappings.get("externalUsers.selectFunctionAccessListByExtUserId"));
                pstmt.setString(1, userId);

                rset = pstmt.executeQuery();
                FunctionAccess functionAccess = null;
                while (rset.next())
                {
                    functionAccess = new FunctionAccess(userId,
                    rset.getInt("Group_ID"),
                    rset.getInt("Function_ID"),
                    ((rset.getInt("FAT_View") == 0) ? true : false),
                    ((rset.getInt("FAT_Modify") == 0) ? true : false),
                    ((rset.getInt("FAT_Delete") == 0) ? true : false));

                    functionAccessMap.put(rset.getString("Function_Name").trim().toLowerCase(), functionAccess);
                }
                user.setFunctionAccessMap(functionAccessMap);
            }
            else
            {
                throw new ScalarException( "Invalid password" );
            }

        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load Users from database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return user;
    }


    public static ArrayList getAllVendorNames() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList vendors = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "externalUsers.getAllVendors" ) );

            rs = pstmt.executeQuery();
            while ( rs.next() )
            {
                String vendor_id = rs.getString( "vendor_id" );
                String vendor_name = rs.getString( "v_name" );

                vendors.add( new org.apache.struts.util.LabelValueBean( vendor_name, vendor_id ) );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Vendors from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException excp )
                {
                }
            }
        }

        return vendors;
    }

    public static int getPackingSlipVendorId( DBTransaction dbTransaction, int customerShippingId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int vendorId = -1;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "externalUsers.getVendorIdByCustomerShippingId" ) );
            pstmt.setInt( 1, customerShippingId );
            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                vendorId = rs.getInt( "Vendor_Id" );
            }

        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not Get External User Id From database", sqle );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return vendorId;
    }

    /**
     * Loads all the function accesses pertaining to the external users.
     *
     * @param dbTransaction
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadAllExternalFunctionalities( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList functionalityList = new ArrayList();
        FunctionAccess functionAccess = null;


        try
        {

            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "externalUsers.getAllExternalFunctionalities" ) );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                functionAccess = new FunctionAccess();
                functionAccess.setFunctionId( rset.getInt( "Function_ID" ) );
                functionAccess.setGroupId( rset.getInt( "Group_ID" ) );
                functionAccess.setFunctionName( rset.getString( "Function_Name" ) );
                functionAccess.setShowView( ( rset.getInt( "FT_View" ) == 0 ) ? true : false );
                functionAccess.setShowModify( ( rset.getInt( "FT_Modify" ) == 0 ) ? true : false );
                functionAccess.setShowDelete( ( rset.getInt( "FT_Delete" ) == 0 ) ? true : false );
                functionalityList.add( functionAccess );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Error while loading the function accesses from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return functionalityList;
    }

    /**
     * Loads the functions and the access levels to which the external user has access to.
     *
     * @param dbTransaction
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadAllExternalUserFunctionAccess( DBTransaction dbTransaction, String externalUserId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList functionAccessList = new ArrayList();
        FunctionAccess functionAccess = null;


        try
        {

            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "externalUsers.getAllExternalUserFunctionAccess" ) );
            pstmt.setString( 1, externalUserId );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                functionAccess = new FunctionAccess();
                functionAccess.setFatId( rset.getInt( "FAT_ID" ) );
                functionAccess.setUserlId( rset.getString( "User_ID" ) );
                functionAccess.setGroupId( rset.getInt( "Group_ID" ) );
                functionAccess.setFunctionId( rset.getInt( "Function_ID" ) );
                functionAccess.setView( ( rset.getInt( "FAT_View" ) == 0 ) ? true : false );
                functionAccess.setModify( ( rset.getInt( "FAT_Modify" ) == 0 ) ? true : false );
                functionAccess.setDelete( ( rset.getInt( "FAT_Delete" ) == 0 ) ? true : false );
                functionAccessList.add( functionAccess );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Error while loading the user function accesses from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return functionAccessList;
    }

    /**
     * Saves the external user function access list.
     *
     * @param dbTransaction
     * @param functionAccessList
     * @throws ScalarException
     */
    public static void saveExternalUserFunctionAccess( DBTransaction dbTransaction, ArrayList functionAccessList ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        FunctionAccess functionAccess = null;
        Iterator functionAccessIterator = functionAccessList.iterator();

        try
        {

            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "externalUsers.saveExternalUsersFunctionAccess" ) );

            while( functionAccessIterator.hasNext() )
            {
                functionAccess = ( FunctionAccess )functionAccessIterator.next();

                pstmt.setInt( 1, functionAccess.getFatId() );
                pstmt.setString( 2, functionAccess.getUserId() );
                pstmt.setInt( 3, functionAccess.getFunctionId() );
                pstmt.setInt( 4, functionAccess.getGroupId() );
                pstmt.setInt( 5, ( ( functionAccess.getView() == true ) ? 0 : 1 ) );
                pstmt.setInt( 6, ( ( functionAccess.getModify() == true ) ? 0 : 1 ) );
                pstmt.setInt( 7, ( ( functionAccess.getDelete() == true ) ? 0 : 1 ) );
                pstmt.executeUpdate();
            }
            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Error while saving the external user function access details", sqle );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }
     public static ArrayList loadAllExternalUsers(boolean loadExpiredAndDisabledUsers) throws ScalarException
     {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList userList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            if (loadExpiredAndDisabledUsers == true) {
                pstmt = conn.prepareStatement("SELECT User_ID from User_Table WHERE USER_TYPE >= 1");
            }
            else {
                pstmt = conn.prepareStatement("SELECT User_ID from User_Table where User_Table.Disabled = 1 and expires_on >= convert(varchar, CURRENT_TIMESTAMP, 101) and User_Table.USER_TYPE >= 1");
            }

            rset = pstmt.executeQuery();
            String userId = null;

            while (rset.next()) {
                userId = rset.getString("User_ID");
                userList.add(new org.apache.struts.util.LabelValueBean(userId, userId));
            }
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load User IDs from database", sqle);
        }

        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
       return userList;
    }
}
